const mysql = require('mysql2');

/*此代码为数据库函数，因为数据库函数均为异步函数，在调用时必须使用回调函数才能获取到函数返回值*/

/*调用示例
  sql.InterfaceFound(interfacetest,(results) =>{
    if(results === false){
        console.log('创建失败');
    }
    else{
        console.log('创建成功');
    }
});
*/

/* 创建数据库连接 */

const connection = mysql.createConnection({

  host: '172.16.32.45',

  user: 'root',

  password: 'WcWurYnU',

  database: 'RUNOOB',

  port:51987

});

function Connection(){

connection.connect(function(err){

    if(err){

        console.log(err);

        return;

    }

    console.log('Connected to database.');

})

}





// 执行用户登录查询函数

function UserQuery(UserName,UserPassword,callback){

Connection();

connection.query('SELECT * FROM users WHERE Username = ? AND Password = ?' , [UserName,UserPassword] , (err, results) => {

  if (err) {

    console.error('Error executing query:', err);

    callback (false);

    return;

  }

  const Role = results[0].Role;

  callback (Role);

});

}



//新建接口

function InterfaceFound(Interface,callback){

Connection();

const sql = 'INSERT INTO interfaces SET ?';

connection.query(sql,Interface,(err,results) =>{

  if(err){

    console.log(err);

    callback (false);

    return;

  }

  callback (true);

})

}



//删除接口

function InterfaceDelete(InterfaceID,callback){

  Connection();

  const sql = 'DELETE FROM interfaces WHERE InterfaceID = ?'

  connection.query(sql,InterfaceID,(err,results)=>{

    if(err){

      console.log(err);

      callback (false);

      return;

    }

    callback (true);

  })

}



//查找接口(使用接口名称)

function InterfaceSeek(InterfaceName,callback){

  Connection();

  const sql = 'SELECT * FROM interfaces WHERE InterfaceName = ?';

  connection.query(sql,InterfaceName,(err,results) =>{

    if(err){

      console.log(err);

      callback (false);

      return;

    }

    callback (results);

  })

}



//修改接口同时将旧接口保存至历史记录中

function InterfaceChange(InterfaceID,Interface,callback){

  Connection();

  sql = 'SELECT * FROM interfaces WHERE InterfaceID = ?'

  connection.query(sql,InterfaceID,(err,results) => {

    if(err){

      console.log(err);

      callback(false);

    }else{

      const OldInterface = results[0];

      const change ={

        InterfaceID:Interface.InterfaceID,

        HistoryInterface:JSON.stringify(OldInterface),

      };

      console.log(change);

      console.log(results);

      sql = 'INSERT INTO changelogs SET ?';

      connection.query(sql,change,(err,results) => {

        if(err){

          console.log(err);

          callback(false);

          return;

        }

        sql = 'UPDATE interfaces SET ? WHERE InterfaceID = ?';
        connection.query(sql,[Interface,InterfaceID],(err,results)=>{
          if(err){
            console.log(err);
            callback (false);
           return;
          }

         callback (true);

        })

      })

    }

  })

}



//查询某个接口的历史接口

function OldInterfaceSeek(InterfaceID,callback){

  Connection();

  const sql = 'SELECT * FROM changelogs WHERE InterfaceID = ?';

  connection.query(sql,InterfaceID,(err,results) => {

    if(err){

      console.log(err);

      callback(false);

      return;

    }

    callback(results);

  })

}



//调用某一个接口的历史接口

function OldInterfaceInvoke(ChangeLogID,callback){

  Connection();

  const sql = 'SELECT * FROM changelogs WHERE ChangeLogID = ?'

  connection.query(sql,ChangeLogID,(err,results) => {

    if(err){

      console.log(err);

      callback(false);

      return;

    }

    callback(results);

  })

}



//创建项目

function ProjectFound(Project,callback){

  Connection();

  sql = 'INSERT INTO projects SET ?'

  connection.query(sql,Project,(err,results) =>{

    if(err){

      console.log(err);

      callback (false);

      return;

    }

      callback(true);

  })

} 



//删除项目及项目相关的接口

function ProjectDELETE(ProjectID,callback){

  Connection();

  sql = 'DELETE FROM projects WHERE ProjectID = ?';

  connection.query(sql,ProjectID,(err,results) =>{

    if(err){

      console.log(err);

      callback (false);

      return;

    }

    sql = 'DELETE FROM interfaces WHERE ProjectID = ?';

    connection.query(sql,ProjectID,(err,results) =>{

      if(err){

        console.log(err);

        callback (false);

        return;

      }

      callback(true);

    })

  })

} 



//修改项目

function ProjectChange(ProjectID,Project,callback){

  Connection();

  sql = 'UPDATE projects SET ? WHERE ProjectID = ?'

  connection.query(sql,[ProjectID,Project],(err,results) =>{

    if(err){

      console.log(err);

      callback (false);

      return

    }

    callback(true);

  })

} 



//查询项目

function ProjectSeek(ProjectName,callback){

  Connection();

  const sql = 'SELECT * FROM projects WHERE ProjectName = ?'

  connection.query(sql,ProjectName,(err,results) =>{

    if(err){

      console.log(err);

      callback(false);

      return

    }

    callback(results);

  })

}



 



module.exports = {

  UserQuery,

  InterfaceFound,

  InterfaceDelete,

  InterfaceSeek,

  InterfaceChange,

  OldInterfaceSeek,

  OldInterfaceInvoke,

  ProjectFound,

  ProjectChange,

  ProjectDELETE,

  ProjectSeek,

};